/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.bodylimit.dao;

import br.com.bodylimit.model.Aluno;
import br.com.bodylimit.model.Serie;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Jõao Paulo
 */
public class AlunoDAOPostgres implements AlunoDAO {

    Connection con;

    public AlunoDAOPostgres(Connection con) {
        this.con = con;
    }

    private List<Aluno> popular(ResultSet rs) throws SQLException {
        List<Aluno> alunos = new ArrayList<Aluno>(0);
        PreparedStatement stmt;
        ResultSet rsTel;
        while (rs.next()) {
            Aluno aluno = popularUm(rs);

            alunos.add(aluno);

            //Obtendo telefones
            stmt = con.prepareStatement("SELECT numero FROM telefone WHERE mat = ?");

            stmt.setInt(1, aluno.getMatricula());

            rsTel = stmt.executeQuery();

            while (rsTel.next()) {
                aluno.getTelefones().add(rsTel.getString("numero"));
            }

        }
        return alunos;
    }


    public Aluno popularUm(ResultSet rs) throws SQLException {
        Aluno aluno = new Aluno();

        //Atribuindo valoles
        aluno.setMatricula(rs.getInt("matAluno"));
        aluno.setNome(rs.getString("nomeAluno"));
        aluno.setEndereco(rs.getString("endereco"));
        aluno.setSexo(rs.getString("sexo").charAt(0));
        aluno.setDiaVencimento(rs.getInt("diaVenc"));
        aluno.setDataNascimento(new Date(rs.getDate("dataNasc").getTime()));

        return aluno;
    }


    public Aluno obter(int matricula) throws SQLException {
        String sql = "SELECT * FROM aluno WHERE matAluno = ?";
        ResultSet rs;
        List<Aluno> alunos;
        PreparedStatement stmt = con.prepareStatement(sql);


        stmt.setInt(1, matricula);
        rs = stmt.executeQuery();


        alunos = popular(rs);

        rs.close();
        stmt.close();

        return alunos.isEmpty() ? null : alunos.get(0);
    }

    public List<Aluno> listarPorNome(String nome) throws SQLException {
        String sql = "SELECT * FROM aluno WHERE nomeAluno LIKE ?";
        ResultSet rs;
        List<Aluno> alunos;
        PreparedStatement stmt = con.prepareStatement(sql);


        stmt.setString(1, nome);
        rs = stmt.executeQuery();


        alunos = popular(rs);

        rs.close();
        stmt.close();

        return alunos;
    }

    public List<Aluno> listar() throws SQLException {
        String sql = "SELECT * FROM aluno";
        ResultSet rs;
        List<Aluno> alunos;
        PreparedStatement stmt = con.prepareStatement(sql);

        rs = stmt.executeQuery();


        alunos = popular(rs);

        rs.close();
        stmt.close();

        return alunos;
    }

    public void adcionar(Aluno aluno) throws SQLException {
        con.setAutoCommit(false);
        ResultSet rs;
        String sql = "INSERT INTO aluno(nomeAluno, endereco, sexo, diaVenc, dataNasc) values(?, ?, ?, ?, ?)";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setString(1, aluno.getNome());
        stmt.setString(2, aluno.getEndereco());
        stmt.setString(3, String.valueOf(aluno.getSexo()));
        stmt.setInt(4, aluno.getDiaVencimento());
        stmt.setDate(5, new java.sql.Date(aluno.getDataNascimento().getTime()));

        stmt.executeUpdate();

        stmt.close();
        con.commit();


        //Obtendo nova matricula
        stmt = con.prepareStatement("SELECT currval FROM currval(\'aluno_mataluno_seq\')");
        rs = stmt.executeQuery();

        if (rs.next()) {
            aluno.setMatricula(rs.getInt("currval"));
        }
        rs.close();
        stmt.close();

        String sqlTelefone = "INSERT INTO telefone(mat, numero) VALUES (?, ?)";
        stmt = con.prepareStatement(sqlTelefone);

        for (String tel : aluno.getTelefones()) {
            stmt.setInt(1, aluno.getMatricula());
            stmt.setString(2, tel);
            stmt.executeUpdate();
        }
        stmt.close();
        con.commit();
    }

    /**
     * Atualiza os dados do aluno na base. NAO altera os telefones.
     * @param aluno
     * @return
     * @throws SQLException
     */
    public boolean atualizar(Aluno aluno) throws SQLException {
        if (aluno.getMatricula() == 0) {
            return false;
        }

        String sql = "UPDATE aluno SET nomeAluno = ?, endereco = ?, sexo = ?, diaVenc = ?, dataNasc = ? WHERE matAluno = ?";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setString(1, aluno.getNome());
        stmt.setString(2, aluno.getEndereco());
        stmt.setString(3, String.valueOf(aluno.getSexo()));
        stmt.setInt(4, aluno.getDiaVencimento());
        stmt.setDate(5, new java.sql.Date(aluno.getDataNascimento().getTime()));
        stmt.setInt(6, aluno.getMatricula());
      

        stmt.executeUpdate();

        stmt.close();

        return true;
    }

    public void remover(int matricula) throws SQLException {
        String sql = "DELETE FROM aluno WHERE matAluno = ?";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setInt(1, matricula);

        stmt.executeUpdate();

        stmt.close();
    }

    public void adcionarTelefone(int matricula, String numero) throws SQLException {
        PreparedStatement stmt;
        String sql = "INSERT INTO telefone(mat, numero) VALUES (?, ?)";

        stmt = con.prepareStatement(sql);
        stmt.setInt(1, matricula);
        stmt.setString(2, numero);

        stmt.executeUpdate();
    }

    public void alterarTelefone(int matricula, String numeroAntigo, String numeroNovo) throws SQLException {
        PreparedStatement stmt;
        String sql = "UPDATE telefone SET numero = ? WHERE mat = ? AND numero = ?";

        stmt = con.prepareStatement(sql);
        stmt.setString(1, numeroNovo);
        stmt.setInt(2, matricula);
        stmt.setString(3, numeroAntigo);

        stmt.executeUpdate();
    }

    public void removerTelefone(int matricula, String numero) throws SQLException {
        PreparedStatement stmt;
        String sql = "DELETE FROM telefone WHERE mat = ? AND numero = ?";

        stmt = con.prepareStatement(sql);
        stmt.setInt(1, matricula);
        stmt.setString(2, numero);

        stmt.executeUpdate();
    }

    public Aluno obterComSeries(int matricula) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void adcionarSerie(Serie serie) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void removerSerie(Serie serie) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void alterarSerie(Serie serie) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
